# _*_ encoding: utf-8 _*_
"""
@File   :   Excel版本对比.py
@Time   :   2024/09/03 11:42:50
@Author :   李道昱
@Desc   :   实现两个Excel文件的版本对比, 两个Excel文件要求列名、列顺序、列格式一致
"""

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import PatternFill
from openpyxl.utils.dataframe import dataframe_to_rows

# 设置填充样式
red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
green_fill = PatternFill(start_color="92D050", end_color="92D050", fill_type="solid")
orange_fill = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid")


def color_fill(worksheet, row_index, dataframe, key, fill_color):
    worksheet.cell(
        row=row_index + 2, column=dataframe.columns.get_loc(key) + 1
    ).fill = fill_color


def compare(old_path, new_path, out_path, keyCol):

    # 读取原数据表和新数据表
    old_df = pd.read_excel(old_path)
    new_df = pd.read_excel(new_path)

    old_keys = set(old_df[keyCol])
    new_keys = set(new_df[keyCol])

    added_keys = new_keys - old_keys
    removed_keys = old_keys - new_keys

    # 确保两表有相同的列名
    if set(old_df.columns) != set(new_df.columns):
        raise ValueError("两个Excel文件的列名不一致")

    columns = new_df.columns

    merged_df = pd.merge(
        old_df, new_df, on=keyCol, how="outer", suffixes=("_旧", "_新")
    )

    # 创建一个新的工作簿
    wb = Workbook()
    ws = wb.active

    # 将DataFrame写入Excel
    for r_idx, row in enumerate(dataframe_to_rows(merged_df, index=False, header=True)):
        for c_idx, value in enumerate(row):
            ws.cell(row=r_idx + 1, column=c_idx + 1, value=value)

    # 遍历合并后的数据框
    for r_idx, row in merged_df.iterrows():
        keyVal = row[keyCol]
        for key in columns:
            if key == keyCol:
                continue
            old_key = f"{key}_旧"
            new_key = f"{key}_新"

            if keyVal in added_keys:  # 新增的行
                color_fill(ws, r_idx, merged_df, new_key, green_fill)

            elif keyVal in removed_keys:  # 删除的行
                color_fill(ws, r_idx, merged_df, old_key, red_fill)

            elif row[old_key] != row[new_key] and not (
                pd.isna(row[old_key]) or pd.isna(row[new_key])
            ):  # 修改的单元格(非空)
                color_fill(ws, r_idx, merged_df, new_key, orange_fill)

    # 保存Excel文件
    wb.save(out_path)


if __name__ == "__main__":
    compare(
        "C:/Users/485807/Downloads/定制需求测试明细表20240903141916.xlsx",  # 原数据表路径
        "C:/Users/485807/Downloads/华东06-08(新).xlsx",  # 新数据表路径
        "C:/Users/485807/Downloads/对比结果.xlsx",  # 对比结果输出路径
        "需求编号",  # 主键
    )
